import 'dart:io';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';

class DatabaseService {
  static Database? _database;
  static const String _dbName = 'media_manager.db';
  static const int _dbVersion = 3;

  static Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  static Future<Database> _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _dbName);
    
    return await openDatabase(
      path,
      version: _dbVersion,
      onCreate: _createTables,
      onUpgrade: _upgradeDatabase,
    );
  }

  static Future<void> _createTables(Database db, int version) async {
    // 创建媒体文件基本信息表
    await db.execute('''
      CREATE TABLE media_files (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        file_path TEXT UNIQUE NOT NULL,
        file_name TEXT NOT NULL,
        file_size INTEGER NOT NULL,
        file_extension TEXT NOT NULL,
        media_type TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        modified_at INTEGER NOT NULL,
        scanned_at INTEGER NOT NULL
      )
    ''');

    // 创建照片元数据表
    await db.execute('''
      CREATE TABLE photo_metadata (
        file_id INTEGER PRIMARY KEY,
        camera_make TEXT,
        camera_model TEXT,
        lens_make TEXT,
        lens_model TEXT,
        focal_length REAL,
        aperture REAL,
        iso INTEGER,
        shutter_speed TEXT,
        width INTEGER,
        height INTEGER,
        gps_latitude REAL,
        gps_longitude REAL,
        date_taken INTEGER,
        FOREIGN KEY (file_id) REFERENCES media_files (id) ON DELETE CASCADE
      )
    ''');

    // 创建视频元数据表
    await db.execute('''
      CREATE TABLE video_metadata (
        file_id INTEGER PRIMARY KEY,
        duration_seconds REAL,
        width INTEGER,
        height INTEGER,
        frame_rate REAL,
        bitrate INTEGER,
        codec TEXT,
        FOREIGN KEY (file_id) REFERENCES media_files (id) ON DELETE CASCADE
      )
    ''');

    // 创建索引
    await db.execute('CREATE INDEX idx_media_type ON media_files (media_type)');
    await db.execute('CREATE INDEX idx_extension ON media_files (file_extension)');
    await db.execute('CREATE INDEX idx_modified ON media_files (modified_at)');
    await db.execute('CREATE INDEX idx_focal_length ON photo_metadata (focal_length)');
    await db.execute('CREATE INDEX idx_aperture ON photo_metadata (aperture)');
    await db.execute('CREATE INDEX idx_iso ON photo_metadata (iso)');
    await db.execute('CREATE INDEX idx_duration ON video_metadata (duration_seconds)');
    await db.execute('CREATE INDEX idx_resolution ON video_metadata (width, height)');
  }

  static Future<void> _upgradeDatabase(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 3) {
      // 获取现有的photo_metadata表结构
      List<Map<String, dynamic>> columns = await db.rawQuery('PRAGMA table_info(photo_metadata)');
      Set<String> existingColumns = columns.map((col) => col['name'] as String).toSet();
      
      // 需要添加的列定义
      Map<String, String> requiredColumns = {
        'camera_make': 'TEXT',
        'camera_model': 'TEXT', 
        'lens_make': 'TEXT',
        'lens_model': 'TEXT',
        'focal_length': 'REAL',
        'aperture': 'REAL',
        'iso': 'INTEGER',
        'shutter_speed': 'TEXT',
        'width': 'INTEGER',
        'height': 'INTEGER',
        'gps_latitude': 'REAL',
        'gps_longitude': 'REAL',
        'date_taken': 'INTEGER',
      };
      
      // 添加缺失的列
      for (String columnName in requiredColumns.keys) {
        if (!existingColumns.contains(columnName)) {
          try {
            String columnType = requiredColumns[columnName]!;
            await db.execute('ALTER TABLE photo_metadata ADD COLUMN $columnName $columnType');
            print('添加列: $columnName');
          } catch (e) {
            print('添加列 $columnName 失败: $e');
          }
        }
      }
    }
  }

  // 插入或更新媒体文件
  static Future<int> insertOrUpdateMediaFile(Map<String, dynamic> mediaFile) async {
    final db = await database;
    return await db.insert(
      'media_files',
      mediaFile,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 插入或更新照片元数据
  static Future<void> insertOrUpdatePhotoMetadata(Map<String, dynamic> metadata) async {
    final db = await database;
    await db.insert(
      'photo_metadata',
      metadata,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 插入或更新视频元数据
  static Future<void> insertOrUpdateVideoMetadata(Map<String, dynamic> metadata) async {
    final db = await database;
    await db.insert(
      'video_metadata',
      metadata,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 获取所有媒体文件
  static Future<List<Map<String, dynamic>>> getAllMediaFiles() async {
    final db = await database;
    return await db.query('media_files', orderBy: 'file_name ASC');
  }

  // 根据类型获取媒体文件
  static Future<List<Map<String, dynamic>>> getMediaFilesByType(String mediaType) async {
    final db = await database;
    return await db.query(
      'media_files',
      where: 'media_type = ?',
      whereArgs: [mediaType],
      orderBy: 'file_name ASC',
    );
  }

  // 获取照片及其EXIF信息
  static Future<List<Map<String, dynamic>>> getPhotosWithMetadata() async {
    final db = await database;
    return await db.rawQuery('''
      SELECT m.*, p.camera_make, p.camera_model, p.lens_make, p.lens_model,
             p.focal_length, p.aperture, p.iso, p.shutter_speed, p.width, 
             p.height, p.gps_latitude, p.gps_longitude, p.date_taken
      FROM media_files m
      LEFT JOIN photo_metadata p ON m.id = p.file_id
      WHERE m.media_type = 'image'
      ORDER BY m.file_name ASC
    ''');
  }

  // 获取视频及其元数据
  static Future<List<Map<String, dynamic>>> getVideosWithMetadata() async {
    final db = await database;
    return await db.rawQuery('''
      SELECT m.*, v.duration_seconds, v.width, v.height, v.frame_rate, 
             v.bitrate, v.codec
      FROM media_files m
      LEFT JOIN video_metadata v ON m.id = v.file_id
      WHERE m.media_type = 'video'
      ORDER BY m.file_name ASC
    ''');
  }

  // 清理不存在的文件记录
  static Future<void> cleanupNonExistentFiles() async {
    final db = await database;
    final files = await db.query('media_files', columns: ['id', 'file_path']);
    
    for (final file in files) {
      final filePath = file['file_path'] as String;
      if (!await File(filePath).exists()) {
        await db.delete('media_files', where: 'id = ?', whereArgs: [file['id']]);
      }
    }
  }

  // 删除指定目录的所有记录
  static Future<void> clearDirectoryRecords(String directoryPath) async {
    final db = await database;
    await db.delete(
      'media_files',
      where: 'file_path LIKE ?',
      whereArgs: ['$directoryPath%'],
    );
  }

  // 检查文件是否已扫描且为最新
  static Future<bool> isFileUpToDate(String filePath, int modifiedTime) async {
    final db = await database;
    final result = await db.query(
      'media_files',
      where: 'file_path = ? AND modified_at >= ?',
      whereArgs: [filePath, modifiedTime],
      limit: 1,
    );
    return result.isNotEmpty;
  }

  // 清空所有扫描数据
  static Future<int> clearAllScannedData() async {
    final db = await database;
    
    // 获取清理前的总数
    final countResult = await db.rawQuery('SELECT COUNT(*) as count FROM media_files');
    final totalCount = countResult.first['count'] as int;
    
    // 清空所有表（由于外键约束，先删除主表即可）
    await db.delete('media_files');
    
    return totalCount;
  }

  // 获取扫描文件统计信息
  static Future<Map<String, int>> getScannedFilesStats() async {
    final db = await database;
    
    final totalResult = await db.rawQuery('SELECT COUNT(*) as count FROM media_files');
    final imageResult = await db.rawQuery("SELECT COUNT(*) as count FROM media_files WHERE media_type = 'image'");
    final videoResult = await db.rawQuery("SELECT COUNT(*) as count FROM media_files WHERE media_type = 'video'");
    
    return {
      'total': totalResult.first['count'] as int,
      'images': imageResult.first['count'] as int,
      'videos': videoResult.first['count'] as int,
    };
  }

  // 关闭数据库
  static Future<void> close() async {
    if (_database != null) {
      await _database!.close();
      _database = null;
    }
  }
}